/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.fiap.dao;

import br.com.fiap.vo.CursoVO;
import br.com.fiap.vo.DisciplinaVO;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Fabianoss
 */
public class DisciplinaDAO {
    
    private DisciplinaVO disciplinaVO;
    private String query;
    private Statement stmt;
    private PreparedStatement pstmt;
    private ResultSet result;
    private FabricaConexao fb;
    
    public DisciplinaDAO(){
        fb = FabricaConexao.getInstance();       
    }
    
    public void Salvar(DisciplinaVO disciplinaVO){
        this.query = "INSERT INTO `avaliacao1`.`disciplina` (`nome`,`curso_idCurso`)VALUES(?,?);";
          try {   
            this.pstmt = fb.getConnection().prepareStatement(query);
            this.pstmt.setString(1, disciplinaVO.getNome());
            this.pstmt.setInt(2,disciplinaVO.getCursoVO().getIdCurso());            
            this.pstmt.execute(); 
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Erro ao inserir Disciplina.: "+ e.getMessage());
        }finally{
              fb.closeConnection(pstmt, null, null);
        }
    }
    
    public void update(DisciplinaVO disciplinaVO){
        this.query = "UPDATE `avaliacao1`.`disciplina`SET `nome` = ?,`curso_idCurso` = ? WHERE  = ?;";
          try {
            this.pstmt = fb.getConnection().prepareStatement(query);
            this.pstmt.setString(1, disciplinaVO.getNome());
            this.pstmt.setInt(2,disciplinaVO.getCursoVO().getIdCurso());
            this.pstmt.setInt(3, disciplinaVO.getIdDisciplina());
            pstmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Erro ao inserir Disciplina.: "+ e.getMessage());
        }finally{
              fb.closeConnection(pstmt, null, null);
        }
    }
    
    public void delete(DisciplinaVO disciplinaVO){        
       this.query = "DELETE FROM `avaliacao1`.`disciplina`WHERE iddisciplina = ?;";
          try {
            this.pstmt = fb.getConnection().prepareStatement(query);
            this.pstmt.setInt(1, disciplinaVO.getIdDisciplina());
            pstmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Erro ao deletar Disciplina.: "+ e.getMessage());
        }finally{
              fb.closeConnection(pstmt, null, null);
        }
        
    }
    
    public List<DisciplinaVO> listarDisciplinaVO(){
        List<DisciplinaVO> lista = new ArrayList<DisciplinaVO>();
        this.query = "SELECT d.iddisciplina,d.nome,c.idcurso,c.nome FROM `avaliacao1`.`disciplina` d, `avaliacao1`.`curso` c where d.curso_idcurso = c.idcurso";
          try {
             this.stmt = fb.getConnection().createStatement();
             this.result = stmt.executeQuery(query);
             
             while(result.next()){
                 DisciplinaVO vo = new DisciplinaVO();
                 vo.setIdDisciplina(result.getInt("d.iddisciplina"));
                 vo.setNome(result.getString("d.nome"));
                 CursoVO cursoVO = new CursoVO();
                 cursoVO.setIdCurso(result.getInt("c.idcurso"));
                 cursoVO.setNome(result.getString("c.nome"));
                 vo.setCursoVO(cursoVO);
                 lista.add(vo);
             }            
            
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Erro ao deletar Disciplina.: "+ e.getMessage());
        }finally{
              fb.closeConnection(null, stmt, result);
        }
        return lista;  
    }
    
     public DisciplinaVO getDisciplinaVOById(int id){
        
        this.query = "SELECT d.iddisciplina,d.nome,c.idcurso,c.nome FROM `avaliacao1`.`disciplina` d, `avaliacao1`.`curso` c where d.curso_idcurso = c.idcurso and d.iddisciplina = ?";
        DisciplinaVO vo = new DisciplinaVO();
        try {
             this.pstmt = fb.getConnection().prepareStatement(query);
             this.pstmt.setInt(1, id);
             this.result = pstmt.executeQuery();             
             while(result.next()){
                
                 vo.setIdDisciplina(result.getInt("d.iddisciplina"));
                 vo.setNome(result.getString("d.nome"));
                 CursoVO cursoVO = new CursoVO();
                 cursoVO.setIdCurso(result.getInt("c.idcurso"));
                 cursoVO.setNome(result.getString("c.nome"));
                 vo.setCursoVO(cursoVO);                 
             }            
            
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Erro ao selecionar Disciplina.: "+ e.getMessage());
        }finally{
              fb.closeConnection(pstmt, null, result);
        }
        return vo;  
    }
    
}

    